USE [HomestayWebDB]
GO

/****** Object:  View [dbo].[Post_ManagePosts_View]    Script Date: 12/18/2012 09:30:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER VIEW [dbo].[Post_ManagePosts_View]
AS
SELECT DISTINCT Posts.PostID
				,Posts.PostStatus
				,Posts.IsAvailable              
                ,Posts.PostAvatar AS AvatarPost
				,Posts.Title
				,Posts.TimeCreated as TimeAdded
				,Posts.ReviewBy
				,Posts.ReviewDate
				,Province.RegionID AS ProvinceID
				,Province.Name AS Province
				,District.RegionID AS DistrictID
				,District.Name AS District				
				,Users.Username
				,Houses.AddressDetail AS AddressHouse
				,Houses.PricePerDay
				,Houses.PricePerWeek
				,Houses.PricePerMonth
				,Users.AvatarPicture			
				,TypeOfHouses.NAME AS TypeOfHousesNAME
				,CountLike.like_count
				,REPLACE(CONVERT(varchar, Posts.TimeCreated, 100), '.', '-') as TimeCreated 
				
FROM Posts
	LEFT JOIN Houses
		ON (Posts.HouseID = Houses.HouseID)
	LEFT JOIN Regions AS Province
		ON (Province.RegionID = Houses.Province)
	LEFT JOIN Regions AS District
		ON (District.RegionID = Houses.District)
	LEFT JOIN TypeOfHouses
		ON  (Houses.TypeOfHouseID = TypeOfHouses.TypeOfHouseID)
	
	LEFT JOIN (SELECT COUNT(Likes.PostID) like_count, Likes.PostID
				FROM POSTS
					LEFT JOIN Likes ON (Posts.PostID = Likes.PostID)
				GROUP BY Likes.PostID) AS CountLike
		ON (Posts.PostID = CountLike.PostID)
	LEFT JOIN Users
		ON (Posts.Username = Users.Username)
	LEFT JOIN (SELECT Tourisms.HouseID, Tourisms.HomeStayTypeID, HomeStayTypes.Name AS HomeStayTypeName
				FROM Tourisms
					LEFT JOIN HomeStayTypes ON (Tourisms.HomeStayTypeID = HomeStayTypes.HomeStayTypeID)
				WHERE HomeStayTypes.IsDeleted = CAST (0 AS BIT))	AS HSType
		ON (Posts.HouseID = HSType.HouseID)
	
WHERE ISNULL(Posts.IsDeleted, CAST(0 AS BIT)) = CAST(0 AS BIT)
	AND Users.IsDeleted = CAST (0 AS BIT)




GO


